package com.kmb.commons.utils;

import org.docx4j.openpackaging.exceptions.Docx4JException;
import org.docx4j.openpackaging.packages.SpreadsheetMLPackage;
import org.docx4j.openpackaging.parts.SpreadsheetML.WorkbookPart;
import org.docx4j.openpackaging.parts.SpreadsheetML.WorksheetPart;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xlsx4j.exceptions.Xlsx4jException;
import org.xlsx4j.org.apache.poi.ss.usermodel.DataFormatter;
import org.xlsx4j.sml.Cell;
import org.xlsx4j.sml.Row;
import org.xlsx4j.sml.SheetData;
import org.xlsx4j.sml.Worksheet;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

/**
 * 使用docx4j解析excel
 */
public class ExcelUtils {
    private static Logger logger = LoggerFactory.getLogger("share");

    /**
     * @return
     */
    public static List<List<String>> parseExcel(File file) {
        List<List<String>> data = new ArrayList<>();
        try {
            SpreadsheetMLPackage xlsxPkg = SpreadsheetMLPackage.load(file);

            WorkbookPart workbookPart = xlsxPkg.getWorkbookPart();
            WorksheetPart sheet = workbookPart.getWorksheet(0);

            DataFormatter formatter = new DataFormatter();

            // Now lets print the cell content
            data = displayContent(sheet, formatter);
        } catch (Xlsx4jException e) {
            logger.error("Excel解析异常", e);
        } catch (Docx4JException e) {
            logger.error("Excel解析异常", e);
        }

        return data;
    }

    private static List<List<String>> displayContent(WorksheetPart sheet, DataFormatter formatter) throws Docx4JException {
        List<List<String>> rowList = new ArrayList<>();
        List<String> cellList;

        Worksheet ws = sheet.getContents();
        SheetData data = ws.getSheetData();

        for (Row r : data.getRow()) {
            //System.out.println("row " + r.getR());
            cellList = new ArrayList<>();

            for (Cell c : r.getC()) {
//	            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//	            System.out.print(cellRef.formatAsString());
//	            System.out.print(" - ");

                // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
                String text = formatter.formatCellValue(c);
                System.out.println(c.getR() + " contains " + text);
                cellList.add(text);
            }

            rowList.add(cellList);
        }

        return rowList;
    }

}
